﻿Imports System.Data.OleDb

Public Class AquariumGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM AquariumListView"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM Aquarium"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM Aquarium LEFT JOIN SiteUser " &
                   "ON Aquarium.UserID=SiteUser.ID " &
                   "WHERE Aquarium.ID=@ID"
        End Get
    End Property

    Public Function FindByTag(ByVal tag As String) As DataTable
        Dim sqlQuery As String = "SELECT DISTINCT AquariumListView.* FROM AquariumListView " &
                                 "INNER JOIN AquariumTagsView ON AquariumListView.ID = AquariumTagsView.ID " &
                                 "WHERE AquariumTagsView.Title LIKE '%' + @TagTitle + '%'"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@TagTitle", tag)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function Insert(ByVal title As String, ByVal description As String, ByVal type As String,
                           ByVal lastCleaned As Date, ByVal cleanPeriod As Integer, ByVal waterTemp As Double,
                           ByVal productID As ULong, ByVal userID As ULong, ByVal photoID As ULong) As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO Aquarium (Title, Description, UserID, LastCleanedDateTime, PeriodDaysCleaning, PhotoID, Temperature, AquariumType, ProductID) " &
                                 "VALUES (@Title, @Description, @UserID, @LastCleanedDateTime, @PeriodDaysCleaning, @PhotoID, @Temperature, @AquariumType, @ProductID)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@Description", description)
                command.Parameters.AddWithValue("@UserID", userID)
                command.Parameters.AddWithValue("@LastCleanedDateTime", lastCleaned)
                command.Parameters.AddWithValue("@PeriodDaysCleaning", cleanPeriod)
                command.Parameters.AddWithValue("@PhotoID", photoID)
                command.Parameters.AddWithValue("@Temperature", waterTemp)
                command.Parameters.AddWithValue("@AquariumType", type)
                command.Parameters.AddWithValue("@ProductID", productID)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function

    Public Sub Update(ByVal aquariumID As ULong, ByVal title As String, ByVal description As String, ByVal type As String,
                      ByVal lastCleaned As Date, ByVal cleanPeriod As Integer, ByVal waterTemp As Double,
                      ByVal productID As ULong, ByVal photoID As ULong)
        Dim sqlQuery As String = "UPDATE Aquarium SET Title=@Title, Description=@Description, LastCleanedDateTime=@LastCleanedDateTime, " &
                                 "PeriodDaysCleaning=@PeriodDaysCleaning, PhotoID=@PhotoID, Temperature=@Temperature, " &
                                 "AquariumType=@AquariumType, ProductID=@ProductID WHERE ID=@AquariumID"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@Description", description)
                command.Parameters.AddWithValue("@LastCleanedDateTime", lastCleaned)
                command.Parameters.AddWithValue("@PeriodDaysCleaning", cleanPeriod)
                command.Parameters.AddWithValue("@PhotoID", photoID)
                command.Parameters.AddWithValue("@Temperature", waterTemp)
                command.Parameters.AddWithValue("@AquariumType", type)
                command.Parameters.AddWithValue("@ProductID", productID)
                command.Parameters.AddWithValue("@AquariumID", aquariumID)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class